package personal.study.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import personal.study.form.LoginForm;

public class DBLogin {

	private static Log log = LogFactory.getLog(DBLogin.class);

	public LoginForm findByPriaryKey(Connection con, String userCode)
			throws Exception {
		LoginForm loginForm = null;
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("SELECT ");
		buffer.append("UserCode,");
		buffer.append("UserName,");
		buffer.append("PassWord,");
		buffer.append("UserSex,");
		buffer.append("UserMobile,");
		buffer.append("ValidStatus,");
		buffer.append("Flag,");
		buffer.append("Remark ");
		buffer.append("FROM User WHERE UserCode='");
		buffer.append(userCode);
		buffer.append("'");

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				loginForm = new LoginForm();
				loginForm.setUserCode(rs.getString("UserCode"));
				loginForm.setUserName(rs.getString("UserName"));
				loginForm.setPassWord(rs.getString("PassWord"));
				loginForm.setUserSex(rs.getString("UserSex"));
				loginForm.setUserMobile(rs.getString("UserMobile"));
				loginForm.setValidStatus(rs.getString("ValidStatus"));
				loginForm.setFlag(rs.getString("Flag"));
				loginForm.setRemark(rs.getString("Remark"));
			}
		} catch (Exception e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}

		return loginForm;
	}

	public Collection<LoginForm> queryResult(Connection con, String conditions,
			int currentPage, int rowsPerPage) throws Exception {
		Collection<LoginForm> col = new ArrayList<LoginForm>();
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("UserCode,");
		buffer.append("UserName,");
		buffer.append("PassWord,");
		buffer.append("UserSex,");
		buffer.append("UserMobile,");
		buffer.append("ValidStatus,");
		buffer.append("Flag,");
		buffer.append("Remark ");
		buffer.append("FROM User WHERE ");
		buffer.append(conditions);
		buffer.append(" LIMIT ");
		buffer.append((currentPage - 1) * rowsPerPage);
		buffer.append(",");
		buffer.append(rowsPerPage);

		log.info(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		LoginForm loginForm = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				loginForm = new LoginForm();
				loginForm.setUserCode(rs.getString("UserCode"));
				loginForm.setUserName(rs.getString("UserName"));
				loginForm.setPassWord(rs.getString("PassWord"));
				loginForm.setUserSex(rs.getString("UserSex"));
				loginForm.setUserMobile(rs.getString("UserMobile"));
				loginForm.setValidStatus(rs.getString("ValidStatus"));
				loginForm.setFlag(rs.getString("Flag"));
				loginForm.setRemark(rs.getString("Remark"));
				col.add(loginForm);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}

		return col;
	}

	public int getCount(Connection con, String conditions) throws Exception {
		int count = 0;
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("SELECT COUNT(*) FROM User WHERE ");
		buffer.append(conditions);

		log.info(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
		} catch (Exception e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}
		return count;
	}

	public boolean insert(Connection con, LoginForm loginform) throws Exception {
		boolean result = false;

		StringBuffer buffer = new StringBuffer(100);
		buffer.append("INSERT INTO User (");
		buffer.append("UserCode,");
		buffer.append("UserName,");
		buffer.append("PassWord,");
		buffer.append("UserSex,");
		buffer.append("UserMobile,");
		buffer.append("ValidStatus,");
		buffer.append("Flag,");
		buffer.append("Remark) ");

		if (log.isDebugEnabled()) {
			StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
			debugBuffer.append(buffer.toString());
			debugBuffer.append("VALUES (");
			debugBuffer.append("'").append(loginform.getUserCode())
					.append("',");
			debugBuffer.append("'").append(loginform.getUserName())
					.append("',");
			debugBuffer.append("'").append(loginform.getPassWord())
					.append("',");
			debugBuffer.append("'").append(loginform.getUserSex()).append("',");
			debugBuffer.append("'").append(loginform.getUserMobile()).append(
					"',");
			debugBuffer.append("'").append(loginform.getValidStatus()).append(
					"',");
			debugBuffer.append("'").append(loginform.getFlag()).append("',");
			debugBuffer.append("'").append(loginform.getRemark()).append("'");
			log.debug(debugBuffer.toString());
		}

		buffer.append("VALUES (?,?,?,?,?,?,?,?)");

		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, loginform.getUserCode());
			ps.setString(2, loginform.getUserName());
			ps.setString(3, loginform.getPassWord());
			ps.setString(4, loginform.getUserSex());
			ps.setString(5, loginform.getUserMobile());
			ps.setString(6, loginform.getValidStatus());
			ps.setString(7, loginform.getFlag());
			ps.setString(8, loginform.getRemark());
			ps.executeUpdate();
			result = true;
		} catch (Exception e) {
			log.error(e.getMessage());
			result = false;
		} finally {
			ps.close();
		}

		return result;
	}

	public void delete(Connection con, String UserCode) throws Exception {
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("DELETE FROM User WHERE ");
		
		if(log.isDebugEnabled()){
			StringBuffer debugBuffer = new StringBuffer(buffer.toString());
			debugBuffer.append("UserCode='");
			debugBuffer.append(UserCode);
			debugBuffer.append("'");
			log.debug(debugBuffer.toString());
		}

		buffer.append("UserCode=?");

		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, UserCode);
			ps.executeUpdate();
		} catch (Exception e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
		}

	}
	public boolean update(Connection con, LoginForm loginform) throws Exception {
        boolean result = false;
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("UPDATE User SET  ");
		buffer.append("UserName=?,");
		buffer.append("PassWord=?,");
		buffer.append("UserSex=?,");
		buffer.append("UserMobile=?,");
		buffer.append("ValidStatus=?, ");
		buffer.append("Flag=?,");
		buffer.append("Remark=? ");
		
		buffer.append("WHERE ");
		buffer.append("UserCode=?");
		
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, loginform.getUserName());
			ps.setString(2, loginform.getPassWord());
			ps.setString(3, loginform.getUserSex());
			ps.setString(4, loginform.getUserMobile());
			ps.setString(5, loginform.getValidStatus());
			ps.setString(6, loginform.getFlag());
			ps.setString(7, loginform.getRemark());
			ps.setString(8, loginform.getUserCode());
			ps.executeUpdate();
			result = true;
		} catch (Exception e) {
			log.error(e.getMessage());
			result = false;
		} finally {
			ps.close();
		}
        return result;
	}
}
